SET client_min_messages='warning';

DROP FUNCTION TableColumnNames(text);
DROP VIEW DBTableNames;
DROP TABLE IF EXISTS Enquiry;
DROP TABLE IF EXISTS Response;
DROP TABLE IF EXISTS Answer;
DROP TABLE IF EXISTS Question;
DROP TABLE IF EXISTS Teaches;
DROP TABLE IF EXISTS AccessTo;
DROP TABLE IF EXISTS ClassAccount;
DROP TABLE IF EXISTS Class;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Subject;
DROP TABLE IF EXISTS Term;
DROP TABLE IF EXISTS IndividualAccount;

CREATE TABLE IndividualAccount (
   id          serial,
   username    text not null,
   password    text not null,
   type        text not null check (type in ('ADM','INS','TUT','STU','OTH')),
   givenName   text not null,
   familyName  text not null,
   unique (username),
   primary key (id)
);

CREATE TABLE Term (
   id          serial,
   session     varchar(2) not null check (session in ('S1','S2','X1','X2','NA')),
   startDate   date not null,
   finishDate  date not null,
   unique (session, startDate, finishDate),
   primary key (id)
);

CREATE TABLE Subject (
   id       serial,
   name     text not null,
   code     varchar(8) not null check ((code ~ '[A-Z]{4}[0-9]{4}') or (code like 'NoCourse')),
   unique (name, code),
   primary key (id)
);

CREATE TABLE Course (
   id       serial,
   subject  integer,
   term     integer,
   unique (subject, term),
   primary key (id),
   foreign key (subject) references Subject (id),
   foreign key (term) references Term (id)
);

CREATE TABLE Class (
   id          serial,
   course      integer,
   startTime   timestamp not null,
   finishTime  timestamp not null,
   week        integer not null check (week > -3), -- -2 = misc, -1 = revision, 0+ = week no.
   name        text not null DEFAULT 'NONE',
   comments    text not null DEFAULT 'NONE',
   primary key (id),
   foreign key (course) references Course (id)
);

CREATE TABLE ClassAccount (
   id          serial,
   class       integer,
   password    text not null,
   unique (class),
   primary key (id),
   foreign key (class) references Class (id)
);

CREATE TABLE AccessTo (
   id          serial,
   account     integer,
   class       integer,
   unique (account, class),
   primary key (id),
   foreign key (account) references IndividualAccount(id),
   foreign key (class) references Class(id)
);

CREATE TABLE Teaches (
   id          serial,
   account     integer,
   class       integer,
   unique (account, class),
   primary key (id),
   foreign key (account) references IndividualAccount(id),
   foreign key (class) references Class(id)
);

CREATE TABLE Question (
   id          serial,
   class       integer,
   number      integer not null,
   description text not null,
   type        varchar(3) not null check (type in ('MCQ','TFQ','OPN')),
   image       text not null DEFAULT 'NONE',
   primary key (id),
   foreign key (class) references Class (id)
);

CREATE TABLE Answer (
   id          serial,
   question    integer,
   description text not null DEFAULT 'NONE',
   isCorrect   boolean not null,
   image       text not null DEFAULT 'NONE',
   primary key (id),
   foreign key (question) references Question (id)
);

CREATE TABLE Response (
   id          serial,
   question    integer,
   description text not null,
   type        varchar(3) not null check (type in ('MCQ','TFQ','OPN')),
   primary key (id),
   foreign key (question) references Question(id)
);

CREATE TABLE Enquiry (
   id          serial,
   class       integer,
   time        timestamp,   
   description text not null,
   primary key (id),
   foreign key (class) references Class (id)
);

CREATE OR REPLACE VIEW DBTableNames (tablenames) AS
(
   SELECT tablename FROM pg_tables WHERE (tablename not like 'pg_%') and (tablename not like 'sql_%')
   --SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
);

CREATE OR REPLACE FUNCTION TableColumnNames(text) RETURNS SETOF text AS
$$
   SELECT lower(column_name) FROM information_schema.columns WHERE table_name = $1;
$$ LANGUAGE SQL;

INSERT INTO Term (session, startDate, finishDate) VALUES 
('NA','0001-01-01', '9999-12-31');

INSERT INTO Subject (name, code) VALUES 
('No Course', 'NoCourse');

INSERT INTO Course (subject, term) VALUES 
(1, 1);

-- EVERYTHING BELOW THIS LINE IS NOT NEEDED FOR THE SYSTEM TO FUNCTION
